const express = require("express");
const router = express.Router();
const db = require("../../utils/db");

// SQL模板相关接口
// 添加SQL模板
router.post("/template/add", (req, res) => {
  const {
    templateName,
    templateCode,
    templateContent,
    description,
    status,
    createdBy,
  } = req.body;
  const sql = `INSERT INTO sql_templates 
    (template_name, template_code, template_content, description, status, created_by) 
    VALUES (?, ?, ?, ?, ?, ?)`;

  db.query(
    sql,
    [
      templateName,
      templateCode,
      templateContent,
      description,
      status,
      createdBy,
    ],
    (error, result) => {
      if (error) {
        res.status(500).json({ error: error.message });
        return;
      }
      res.json({ id: result.insertId });
    }
  );
});

// 添加模板参数
router.post("/template/param/add", (req, res) => {
  const {
    templateId,
    paramName,
    paramCode,
    paramType,
    isRequired,
    defaultValue,
    description,
    createdBy,
  } = req.body;
  const sql = `INSERT INTO template_params 
    (template_id, param_name, param_code, param_type, is_required, default_value, description, created_by) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;

  db.query(
    sql,
    [
      templateId,
      paramName,
      paramCode,
      paramType,
      isRequired,
      defaultValue,
      description,
      createdBy,
    ],
    (error, result) => {
      if (error) {
        res.status(500).json({ error: error.message });
        return;
      }
      res.json({ id: result.insertId });
    }
  );
});

// 生成SQL记录
router.post("/record/add", (req, res) => {
  const { templateId, requestParams, generatedSql, createdBy } = req.body;
  const sql = `INSERT INTO sql_generation_records 
    (template_id, request_params, generated_sql, created_by) 
    VALUES (?, ?, ?, ?)`;

  db.query(
    sql,
    [templateId, JSON.stringify(requestParams), generatedSql, createdBy],
    (error, result) => {
      if (error) {
        res.status(500).json({ error: error.message });
        return;
      }
      res.json({ id: result.insertId });
    }
  );
});

// 添加数据源配置
router.post("/datasource/add", (req, res) => {
  const {
    dsName,
    dsCode,
    dsType,
    jdbcUrl,
    username,
    password,
    status,
    createdBy,
  } = req.body;
  const sql = `INSERT INTO data_source_configs 
    (ds_name, ds_code, ds_type, jdbc_url, username, password, status, created_by) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;

  db.query(
    sql,
    [dsName, dsCode, dsType, jdbcUrl, username, password, status, createdBy],
    (error, result) => {
      if (error) {
        res.status(500).json({ error: error.message });
        return;
      }
      res.json({ id: result.insertId });
    }
  );
});

// 查询SQL模板列表
router.get("/template/list", (req, res) => {
  const { page = 1, pageSize = 10, status } = req.query;
  const offset = (page - 1) * pageSize;

  let sql = `SELECT * FROM sql_templates WHERE 1=1`;
  const params = [];

  if (status !== undefined) {
    sql += ` AND status = ?`;
    params.push(status);
  }

  sql += ` ORDER BY created_time DESC LIMIT ? OFFSET ?`;
  params.push(parseInt(pageSize), offset);

  db.query(sql, params, (error, results) => {
    if (error) {
      res.status(500).json({ error: error.message });
      return;
    }
    res.json(results);
  });
});

// 查询模板参数列表
router.get("/template/param/list", (req, res) => {
  const { templateId } = req.query;

  const sql = `SELECT * FROM template_params WHERE template_id = ? ORDER BY id`;

  db.query(sql, [templateId], (error, results) => {
    if (error) {
      res.status(500).json({ error: error.message });
      return;
    }
    res.json(results);
  });
});

// 查询数据源列表
router.get("/datasource/list", (req, res) => {
  const { page = 1, pageSize = 10, status } = req.query;
  const offset = (page - 1) * pageSize;

  let sql = `SELECT id, ds_name, ds_code, ds_type, status, created_by, created_time, updated_by, updated_time 
    FROM data_source_configs WHERE 1=1`;
  const params = [];

  if (status !== undefined) {
    sql += ` AND status = ?`;
    params.push(status);
  }

  sql += ` ORDER BY created_time DESC LIMIT ? OFFSET ?`;
  params.push(parseInt(pageSize), offset);

  db.query(sql, params, (error, results) => {
    if (error) {
      res.status(500).json({ error: error.message });
      return;
    }
    res.json(results);
  });
});

module.exports = router;
